package com.wy.component.office;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.wy.component.dataConvert.DateUtil;

public class ExcelUtils {

	public static final String CSV = "csv";
	public static final String XLSX = "xlsx";

	/**
	 * 合并单元格后给合并后的单元格加边框
	 */
	public static void setRegionStyle(XSSFSheet sheet, CellRangeAddress region,
			XSSFCellStyle cs) {
		for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
			XSSFRow row = sheet.getRow(i);
			for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
				XSSFCell cell = row.getCell(j);
				cell.setCellStyle(cs);
			}
		}
	}

	/**
	 * 设置表头的单元格样式
	 */
	public static XSSFCellStyle getHeadStyle(XSSFWorkbook wb) {
		// 创建单元格样式
		XSSFCellStyle cellStyle = wb.createCellStyle();
		// 设置单元格的背景颜色为淡蓝色
		cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// 设置单元格居中对齐
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		XSSFFont font = wb.createFont();
		// 设置字体加粗
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 设置单元格边框为细线条
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		return cellStyle;
	}

	/**
	 * 设置表体的单元格样式
	 */
	public static XSSFCellStyle getBodyStyle(XSSFWorkbook wb) {
		// 创建单元格样式
		XSSFCellStyle cellStyle = wb.createCellStyle();
		// 设置单元格居中对齐
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		XSSFFont font = wb.createFont();
		// 设置字体加粗
		// font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 设置单元格边框为细线条
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		return cellStyle;
	}

	/**
	 * 生成文件内容
	 */
	public static <T> XSSFWorkbook writeToExcel(List<T> list,
			String sheetTitle, int columnTotal, String[] titles,
			int[] columnWidths, WriteCallback<T> callback) throws IOException {
		// 创建一个workbook 对应一个excel应用文件
		XSSFWorkbook workbook = new XSSFWorkbook();
		// 在workbook中增加一个sheet,对应Excel文件中的sheet
		XSSFSheet sheet = workbook.createSheet(sheetTitle);
		XSSFCellStyle headStyle = getHeadStyle(workbook);
		XSSFCellStyle bodyStyle = getBodyStyle(workbook);
		// 构建表头
		XSSFRow headRow = sheet.createRow(0);
		XSSFCell cell = null;
		for (int i = 0; i < titles.length; i++) {
			cell = headRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(titles[i]);

			sheet.setColumnWidth(i, columnWidths[i]);
		}
		// 构建表体数据
		if (list != null && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				XSSFRow bodyRow = sheet.createRow(i + 1);
				T obj = list.get(i);
				String[] cells = callback.getRowValue(i, columnTotal, obj);
				for (int j = 0; j < columnTotal; j++) {
					cell = bodyRow.createCell(j);
					cell.setCellStyle(bodyStyle);
					cell.setCellValue(cells[j]);
				}
			}
		}
		return workbook;
	}

	public static Workbook createWorkbook(InputStream in) throws IOException,
			InvalidFormatException, ExcelException {
		if (!in.markSupported()) {
			in = new PushbackInputStream(in, 8);
		}
		if (POIFSFileSystem.hasPOIFSHeader(in)) {
			return new HSSFWorkbook(in);
		}
		if (POIXMLDocument.hasOOXMLHeader(in)) {
			return new XSSFWorkbook(OPCPackage.open(in));
		}
		throw new ExcelException();
	}

	public static <T> List<T> readFromExcel(InputStream in, Class<T> clazz,
			int columnTotal, ReadCallback<T> callback) throws IOException,
			InvalidFormatException, ExcelException {
		List<T> list = new ArrayList<T>();
		try {
			Workbook workBook = createWorkbook(in);

			Sheet sheet = workBook.getSheetAt(0);
			T obj = null;
			Row row = null;
			String cells[] = new String[columnTotal];
			Cell cell = null;
			if (sheet != null) {
				for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
					row = sheet.getRow(i);
					try {
						obj = clazz.newInstance();
					} catch (InstantiationException e) {
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					}
					cells = new String[columnTotal];
					cell = null;
					for (int j = 0; j < row.getPhysicalNumberOfCells()
							&& j < columnTotal; j++) {
						cell = row.getCell(j);
						if (cell != null) {
							if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()
									&& org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
								cells[j] = DateUtil.formatDateTime(cell
										.getDateCellValue());
							} else {
								cell.setCellType(Cell.CELL_TYPE_STRING);
								cells[j] = StringUtils.trimToEmpty(cell
										.getStringCellValue());
							}
						}
					}
					callback.setRowValue(i, columnTotal, obj, cells);
					list.add(obj);
				}
			}
		} finally {
			if (null != in) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}

//	public static <T> List<T> readFromExcel(InputStream in, Class<T> clazz,
//			int columnTotal, ReadCallback<T> callback, String fileType)
//			throws IOException, InvalidFormatException, ExcelException {
//		if (CSV.equalsIgnoreCase(fileType)) {
//			return CSVUtils.readFromCSV(in, clazz, columnTotal, callback);
//		} else {
//			return readFromExcel(in, clazz, columnTotal, callback);
//		}
//	}
	
	public static <T> List<T> importDataExcel(InputStream in, Class<T> clazz,
			int columnTotal, ReadCallback<T> callback, int startLineNumber)
			throws IOException, InvalidFormatException, ExcelException {
		List<T> list = new ArrayList<T>();
		try {
			Workbook workBook = createWorkbook(in);
			Sheet sheet = workBook.getSheetAt(0);
			T obj = null;
			Row row = null;
			String cells[] = new String[columnTotal];
			Cell cell = null;
			if (sheet != null) {
				for (int i = startLineNumber; i < sheet.getPhysicalNumberOfRows(); i++) {
					row = sheet.getRow(i);
					try {
						obj = clazz.newInstance();
					} catch (InstantiationException e) {
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					}
					cells = new String[columnTotal];
					cell = null;
					for (int j = 0; j < columnTotal; j++) {
						cell = row.getCell(j);
						if (cell != null) {
							if (Cell.CELL_TYPE_NUMERIC == cell.getCellType() && org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
								cells[j] = DateUtil.formatDateTime(cell.getDateCellValue());
							} else {
								cell.setCellType(Cell.CELL_TYPE_STRING);
								cells[j] = StringUtils.trimToEmpty(cell.getStringCellValue());
							}
						}
					}
					callback.setRowValue(i, columnTotal, obj, cells);
					list.add(obj);
				}
			}
		} finally {
			if (null != in) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return list;
	}
}